/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package apus.persistence.jdbc;

import apus.entity.*;
import apus.exception.DatabaseException;
import apus.exception.EntityNotFoundException;
import apus.persistence.AccountDAO;
import apus.persistence.OrganizationDAO;
import apus.persistence.PeriodDAO;
import apus.persistence.PersonDAO;
import apus.persistence.proxy.OrganizationProxy;
import apus.persistence.proxy.PeriodProxy;
import apus.persistence.proxy.PersonProxy;
import apus.services.ServiceLocator;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * @author Roman Dyatkovsky
 * @author Maxim Vasilevsky
 * @since APUS v0.5
 */
public class JdbcAccountDAO extends AbstractJdbcDAO<Account> implements AccountDAO {

    @Override
    public Account read(int id) {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        Account account = null;
        try {
            st = cn.createStatement();
            rs = st.executeQuery("select * from ACCOUNT where ID_ACCOUNT=" + id);
            int typeSub;

            if (rs.next()) {
                rs2 = st.executeQuery("select SUBSCRIBER_TYPE from SUBSCRIBER where ID_SUBSCRIBER=" + rs.getInt("SUBSCRIBER_ID"));
                rs2.next();
                typeSub = rs2.getInt("SUBSCRIBER_TYPE");

                int subscriber = rs.getInt("SUBSCRIBER_ID");
                int period = rs.getInt("PERIOD_ID");

                account = new RealAccount();
                account.setId(rs.getInt("ID_ACCOUNT"));
                account.setPay(rs.getBoolean("IS_PAID"));

                Subscriber sub = null;

                if (subscriber != 0) {
                    if (typeSub == 0) {
                        sub = new PersonProxy();
                    } else {
                        sub = new OrganizationProxy();
                    }
                    sub.setId(subscriber);
                }
                account.setSubscriber(sub);


                Period per = new PeriodProxy();
                per.setId(period);
                account.setPeriod(per);
            }
        } catch (SQLException ex) {
            throw new DatabaseException("Can't read account with ID=" + id, ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
        if (account == null) {
            throw new EntityNotFoundException("Account with ID=" + id + " not found");
        }
        account.calculate();
        return account;
    }

    @Override
    public void update(Account entity) {
        Connection cn = getConnection();
        PreparedStatement st = null;
        try {
            st = cn.prepareStatement("update ACCOUNT set SUBSCRIBER_ID=?, PERIOD_ID=?, IS_PAID=? "
                    + " where ID_ACCOUNT=?");
            st.setInt(1, entity.getSubscriber().getId());
            st.setInt(2, entity.getPeriod().getId());
            st.setBoolean(3, entity.isPaid());
            st.setInt(4, entity.getId());
            st.execute();

        } catch (SQLException e) {
            throw new DatabaseException("Can't update account with ID=" + entity.getId(), e);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    @Override
    public void delete(Account entity) {
        Connection cn = getConnection();
        PreparedStatement st = null;
        try {
            st = cn.prepareStatement("delete from ACCOUNT where ID_ACCOUNT=?");
            st.setInt(1, entity.getId());
            st.execute();
        } catch (SQLException e) {
            throw new DatabaseException("Can't delete account with ID=" + entity.getId(), e);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    @Override
    public List<Account> findAll() {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        List<Account> accountList = new ArrayList<>();
        try {
            st = cn.createStatement();
            rs = st.executeQuery("select * from ACCOUNT");
            int typeSub;

            while (rs.next()) {
                Account account;
                rs2 = st.executeQuery("select SUBSCRIBER_TYPE from SUBSCRIBER where ID_SUBSCRIBER=" + rs.getInt("SUBSCRIBER_ID"));
                rs2.next();
                typeSub = rs2.getInt("SUBSCRIBER_TYPE");

                int subscriber = rs.getInt("SUBSCRIBER_ID");
                int period = rs.getInt("PERIOD_ID");

                account = new RealAccount();
                account.setId(rs.getInt("ID_ACCOUNT"));
                account.setPay(rs.getBoolean("IS_PAID"));

                Subscriber sub = null;

                if (subscriber != 0) {
                    if (typeSub == 0) {
                        sub = new PersonProxy();
                    } else {
                        sub = new OrganizationProxy();
                    }
                    sub.setId(subscriber);
                }
                account.setSubscriber(sub);

                Period per = new PeriodProxy();
                per.setId(period);
                account.setPeriod(per);

                account.calculate();
                accountList.add(account);
            }
        } catch (SQLException ex) {
            throw new DatabaseException("Can't read account list", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (rs2 != null) {
                    rs2.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
        if (accountList.isEmpty()) {
            throw new EntityNotFoundException("Accounts not found");
        }
        return accountList;
    }

    @Override
    public void create(Account entity) {
        entity.setId(getNextId());
        entity.calculate();

        Connection cn = getConnection();
        PreparedStatement st = null;
        try {
            try {
                readBySubscriberAndPeriod(entity.getSubscriber(), entity.getPeriod());
            } catch (EntityNotFoundException e) {
                st = cn.prepareStatement("insert into ACCOUNT (ID_ACCOUNT, SUBSCRIBER_ID, PERIOD_ID, IS_PAID) VALUES (?,?,?,?)");
                st.setInt(1, entity.getId());
                st.setInt(2, entity.getSubscriber().getId());
                st.setInt(3, entity.getPeriod().getId());
                st.setBoolean(4, entity.isPaid());
                st.execute();
            }
        } catch (SQLException e) {
            throw new DatabaseException("Can't create new account", e);
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    @Override
    public Account readBySubscriberAndPeriod(Subscriber subscriber, Period period) {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        Account account = null;
        try {
            st = cn.createStatement();
            rs = st.executeQuery("select * from ACCOUNT where SUBSCRIBER_ID=" + subscriber.getId() + " AND "
                    + "PERIOD_ID=" + period.getId());
            int typeSub;

            if (rs.next()) {
                rs2 = st.executeQuery("select SUBSCRIBER_TYPE from SUBSCRIBER where ID_SUBSCRIBER=" + subscriber.getId());
                rs2.next();
                typeSub = rs2.getInt("SUBSCRIBER_TYPE");
                int subscriberLoc = rs.getInt("SUBSCRIBER_ID");
                int periodLoc = rs.getInt("PERIOD_ID");

                account = new RealAccount();
                account.setId(rs.getInt("ID_ACCOUNT"));
                account.setPay(rs.getBoolean("IS_PAID"));

                Subscriber sub = null;

                if (subscriberLoc != 0) {
                    if (typeSub == 0) {
                        sub = new PersonProxy();
                    } else {
                        sub = new OrganizationProxy();
                    }
                    sub.setId(subscriberLoc);
                }
                account.setSubscriber(sub);


                Period per = new PeriodProxy();
                per.setId(periodLoc);
                account.setPeriod(per);
            }
        } catch (SQLException ex) {
            throw new DatabaseException("Can't read account", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
        if (account == null) {
            throw new EntityNotFoundException("Account not found");
        }
        return account;
    }

    @Override
    public void calculatePrice(Integer id) {
        Account account = read(id);
        account.calculate();
        update(account);
    }

    @Override
    public void calculateAllPrices() {
        for (Account account : findAll()) {
            calculatePrice(account.getId());
        }
    }

    @Override
    public List<Account> findBySubscriber(String parameter) {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        List<Account> list = new ArrayList<>();
        try {
            st = cn.createStatement();
            rs = st.executeQuery("SELECT * FROM ACCOUNT "
                    + "JOIN SUBSCRIBER ON ID_SUBSCRIBER=SUBSCRIBER_ID "
                    + "WHERE lower(SUBSCRIBER_NAME) like '%" + parameter.toLowerCase() + "%'");
            while (rs.next()) {

                Account account;
                rs2 = st.executeQuery("select SUBSCRIBER_TYPE from SUBSCRIBER where ID_SUBSCRIBER=" + rs.getInt("SUBSCRIBER_ID"));
                rs2.next();
                int typeSub = rs2.getInt("SUBSCRIBER_TYPE");

                int subscriber = rs.getInt("SUBSCRIBER_ID");
                int period = rs.getInt("PERIOD_ID");

                account = new RealAccount();
                account.setId(rs.getInt("ID_ACCOUNT"));
                account.setPay(rs.getBoolean("IS_PAID"));

                Subscriber sub = null;

                if (subscriber != 0) {
                    if (typeSub == 0) {
                        sub = new PersonProxy();
                    } else {
                        sub = new OrganizationProxy();
                    }
                    sub.setId(subscriber);
                }
                account.setSubscriber(sub);

                Period per = new PeriodProxy();
                per.setId(period);
                account.setPeriod(per);

                account.calculate();
                list.add(account);

            }
        } catch (SQLException e) {
            throw new DatabaseException("Can't find accounts", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (rs2 != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }

        if (list.isEmpty()) {
            throw new EntityNotFoundException("Matches not found");
        }
        return list;
    }

    @Override
    public int addMonthlyAccounts() {
        int i=0;
        
        // Creating period from 1st number of previous month to 1st number of current month
        Calendar calendar = Calendar.getInstance();
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        calendar.set(Calendar.HOUR, -12);
        calendar.set(Calendar.MINUTE, 0);
        calendar.set(Calendar.SECOND, 0);

        Date end = calendar.getTime();
        calendar.add(Calendar.MONTH, -1);
        Date start = calendar.getTime();



        PersonDAO perDAO = ServiceLocator.getService(PersonDAO.class);
        OrganizationDAO orgDAO = ServiceLocator.getService(OrganizationDAO.class);
        PeriodDAO periodDAO = ServiceLocator.getService(PeriodDAO.class);

        Period period;

        try {
            period = periodDAO.readByStartAndEndDates(start, end);
        } catch (EntityNotFoundException e) {
            period = new RealPeriod(start, end);
            periodDAO.create(period);
        }



        for (Person per : perDAO.findAll()) {
            Account acc = new RealAccount(per, period);
            acc.calculate();
            create(acc);
            i++;
        }

        for (Organization org : orgDAO.findAll()) {
            Account acc = new RealAccount(org, period);
            acc.calculate();
            create(acc);
            i++;
        }
        return i;
    }

    @Override
    public void payAccount(Integer id) {
                Connection cn = getConnection();
        PreparedStatement st = null;
        try {
            st = cn.prepareStatement("update ACCOUNT set IS_PAID=true where ID_ACCOUNT=?");
            st.setInt(1, id);
            st.execute();

        } catch (SQLException e) {
            throw new DatabaseException("Can't pay account with ID=" + id, e);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

@Override
    public List<Account> readBySubscriber(Integer id) {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        Account account = null;
        List <Account> accountList = new ArrayList<>();
        try {
            st = cn.createStatement();
            rs = st.executeQuery("select * from ACCOUNT where SUBSCRIBER_ID=" + id +";");
            int typeSub;

            while (rs.next()) {
                rs2 = st.executeQuery("select SUBSCRIBER_TYPE from SUBSCRIBER where ID_SUBSCRIBER=" + id);
                rs2.next();
                typeSub = rs2.getInt("SUBSCRIBER_TYPE");
                int subscriberLoc = rs.getInt("SUBSCRIBER_ID");
                int periodLoc = rs.getInt("PERIOD_ID");

                account = new RealAccount();
                account.setId(rs.getInt("ID_ACCOUNT"));
                account.setPay(rs.getBoolean("IS_PAID"));

                Subscriber sub = null;

                if (subscriberLoc != 0) {
                    if (typeSub == 0) {
                        sub = new PersonProxy();
                    } else {
                        sub = new OrganizationProxy();
                    }
                    sub.setId(subscriberLoc);
                }
                account.setSubscriber(sub);


                Period per = new PeriodProxy();
                per.setId(periodLoc);
                account.setPeriod(per);
                
                account.calculate();
                
                accountList.add(account);
            }
        } catch (SQLException ex) {
            throw new DatabaseException("Can't read account", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
      /*  if (accountList == null) {
            throw new EntityNotFoundException("Account not found");
        }*/
        return accountList;
    }
}
